package com.demon.framework.util.excel;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Font;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.TableStyle;
import com.demon.framework.common.constants.BatchConstants;
import com.demon.framework.util.data.JdbcForBigDataUtil;
import com.google.common.base.Charsets;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.apache.http.entity.ContentType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.http.HttpHeaders;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * excel导出工具类
 *
 * @author Demon-HY
 * @date 2020/7/27 14:46
 */
@Slf4j
public class ExcelExportUtil {

    /**
     * 金色的 表单样式
     */
    public final static TableStyle TABLE_STYLE_GOLD = createGoldTableStyle();

    private final static String SHEET_NAME_DEFAULT = "sheet";
    private final static int HEAD_LINE_NUM_DEFAULT = 1;

    /**
     * 根据model数据 将excel输入response
     *
     * @param response
     * @param models
     * @param exportFileName
     */
    public static void responseExcel(HttpServletResponse response, List<? extends BaseRowModel> models, String exportFileName) {
        responseExcel(response, models, exportFileName, TABLE_STYLE_GOLD);
    }

    /**
     * 根据model数据 将excel输入response
     *
     * @param response
     * @param models
     * @param tableStyle
     * @param exportFileName
     */
    public static void responseExcel(HttpServletResponse response, List<? extends BaseRowModel> models, String exportFileName, TableStyle tableStyle) {
        try {
            ServletOutputStream out = response.getOutputStream();
            responseExcel(response, exportFileName);
            export(out, models, tableStyle);
            out.flush();
        } catch (Exception e) {
            log.error("导出excel系统异常", e);
        }

    }

    /**
     * http导出excel
     *
     * @param response
     * @param exportFileName
     * @throws IOException
     */
    private static void responseExcel(HttpServletResponse response, String exportFileName) throws IOException {
        response.setContentType(ContentType.APPLICATION_OCTET_STREAM.getMimeType());
        response.setCharacterEncoding(Charsets.UTF_8.name());
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + exportFileName + ".xlsx");
    }

    /**
     * 根据model数据 将excel输入outputStream
     *
     * @param outputStream
     * @param models
     * @param tableStyle
     */
    public static void export(OutputStream outputStream, List<? extends BaseRowModel> models, TableStyle tableStyle) {
        ExcelWriter writer = EasyExcelFactory.getWriter(outputStream);

        Class<? extends BaseRowModel> modelClass = models.get(0).getClass();

        Sheet sheet = getSheet(modelClass, tableStyle);
        writer.write(models, sheet);
        writer.finish();
    }


    /**
     * 根据sql 将excel输入response
     *
     * @param response
     * @param dataSource
     * @param sql            sql的字段名为列名,支持重命名 ,重命名请不要使用引号 eg: select id 主键 from table
     * @param exportFileName
     */
    public static void responseExcel(HttpServletResponse response, DataSource dataSource, String sql, String exportFileName) {
        responseExcel(response, dataSource, sql, exportFileName, TABLE_STYLE_GOLD);
    }


    /**
     * 根据sql 将excel输入response
     *
     * @param response
     * @param dataSource
     * @param sql            sql的字段名为列名,支持重命名 ,重命名请不要使用引号 eg: select id 主键 from table
     * @param tableStyle
     * @param exportFileName
     */
    public static void responseExcel(HttpServletResponse response, DataSource dataSource, String sql, String exportFileName, TableStyle tableStyle) {
        try {
            ServletOutputStream out = response.getOutputStream();
            responseExcel(response, exportFileName);
            export(out, dataSource, sql, tableStyle);
            out.flush();
        } catch (Exception e) {
            log.error("导出excel系统异常", e);
        }
    }

    /**
     * 根据sql 将excel输入outputStream
     *
     * @param outputStream
     * @param dataSource
     * @param sql          sql的字段名为列名,支持重命名 ,重命名请不要使用引号 eg: select id 主键 from table
     * @param tableStyle
     */
    public static void export(OutputStream outputStream, DataSource dataSource, String sql, TableStyle tableStyle) {
        ExcelWriter writer = EasyExcelFactory.getWriter(outputStream);

        Sheet sheet = getSheet(sql, tableStyle);

        //已处理的条数
        AtomicInteger hasProcessCount = new AtomicInteger(0);
        //通过sql获取数据
        try {
            JdbcForBigDataUtil.processData(dataSource, sql, datas -> {
                writer.write1(datas, sheet);

                //1000条刷新一下导出流
                if (hasProcessCount.addAndGet(1) % BatchConstants.BATCH_SIZE_DEFAULT == 0) {
                    outputStream.flush();
                }
            });
        } catch (Throwable e) {
            log.error("导出excel失败,sql:{}", sql, e);
            List<List<Object>> wanrMsgList = Lists.newArrayList();
            List<Object> warnMsg = Lists.newArrayList();
            wanrMsgList.add(warnMsg);
            warnMsg.add("数据未全部导出,可缩小查询范围再重试！");
            for (int i = 1; i < sheet.getHead().size(); i++) {
                warnMsg.add("");
            }
            sheet.getTableStyle().setTableContentBackGroundColor(IndexedColors.RED);
            writer.write1(wanrMsgList, sheet);
            //需要合并单元格
            writer.merge(hasProcessCount.get() + 1, hasProcessCount.get() + 2, 0, sheet.getHead().size());
        }

        writer.finish();
    }

    /**
     * modelClass 获取 Sheet
     */
    public static Sheet getSheet(Class<? extends BaseRowModel> modelClass) {
        return getSheet(modelClass, TABLE_STYLE_GOLD);
    }

    public static Sheet getSheet(Class<? extends BaseRowModel> modelClass, TableStyle tableStyle) {
        int headLineNum = HEAD_LINE_NUM_DEFAULT;

        for (Field field : modelClass.getDeclaredFields()) {
            if (field.isAnnotationPresent(ExcelProperty.class)) {
                ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                headLineNum = Math.max(annotation.value().length, headLineNum);
            }
        }

        Sheet sheet = new Sheet(1, headLineNum, modelClass, SHEET_NAME_DEFAULT, null);
        sheet.setTableStyle(tableStyle);
        sheet.setAutoWidth(true);

        return sheet;
    }

    /**
     * 根据sql 获取 Sheet
     */
    public static Sheet getSheet(String sql) {
        return getSheet(sql, TABLE_STYLE_GOLD);
    }

    public static Sheet getSheet(String sql, TableStyle tableStyle) {
        Sheet sheet = new Sheet(1, HEAD_LINE_NUM_DEFAULT);
        sheet.setHead(getHeadBySql(sql));
        sheet.setTableStyle(tableStyle);
        sheet.setAutoWidth(true);

        return sheet;
    }

    /**
     * 通过sql 获取 一级标题 表头数据
     */
    private static List<List<String>> getHeadBySql(String sql) {
        List<String> columnNames = JdbcForBigDataUtil.getColumnNames(sql);
        List<List<String>> head = new ArrayList<>();

        columnNames.forEach(columnName -> {
            List<String> headName = Lists.newArrayList(columnName);
            head.add(headName);
        });

        return head;
    }

    /**
     * 创建金色标题的 表格样式
     */
    private static TableStyle createGoldTableStyle() {
        TableStyle tableStyle = new TableStyle();
        Font headFont = new Font();
        headFont.setBold(true);
        headFont.setFontHeightInPoints((short) 22);
        headFont.setFontName("楷体");
        tableStyle.setTableHeadFont(headFont);
        tableStyle.setTableHeadBackGroundColor(IndexedColors.GOLD);

        Font contentFont = new Font();
        contentFont.setBold(false);
        contentFont.setFontHeightInPoints((short) 18);
        contentFont.setFontName("黑体");
        tableStyle.setTableContentFont(contentFont);
        tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
        return tableStyle;
    }
}
